﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.IO;

namespace RH.EBS.Core.Repository.DBUtility
{
    public class SqlDBAccess
    {
        public static string DBConnectionString = ConfigurationManager.AppSettings.Get("DBConnection");

        public bool CancelFlag = false;
        public static string dbName = "zgsoft";

        public SqlDBAccess()
        {

        }
        public SqlDBAccess(string connStr)
        {
            DBConnectionString = connStr;
        }
        private SqlConnection CreateDBConnection()
        {
            if (!string.IsNullOrEmpty(dbName))
            {
                string[] connStrs = DBConnectionString.Split(new char[] { ';' });
                DBConnectionString = connStrs[0] + ";Initial Catalog=" + dbName + ";" + connStrs[2] + ";" + connStrs[3] + ";" + connStrs[4] + ";" + connStrs[5] + ";" + connStrs[6] + ";" + connStrs[7];
            }
            SqlConnection myConn = new SqlConnection(DBConnectionString);
            return myConn;
        }


        #region 基本sql操作，以字符串Sql形式操作数据库。
        /// <summary>
        /// 打开单独数据表获取数据集，并返回DataSet。
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public DataSet OpenTable(string TableName)
        {
            SqlConnection myConn = this.CreateDBConnection();
            DataSet myDataSet = new DataSet();
            SqlDataAdapter myDataAdapter;

            try
            {
                myConn.Open();
                myDataAdapter = new SqlDataAdapter("select * from " + TableName, myConn);
                myDataAdapter.Fill(myDataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return myDataSet;
        }

        /// <summary>
        /// 可执行复杂的sql语句获取多表或多字段的数据集，并返回DataSet。
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public DataSet ExecuteSql(string StrSql)
        {
            SqlConnection myConn = this.CreateDBConnection();
            DataSet myDataSet = new DataSet();
            SqlDataAdapter myDataAdapter;

            try
            {
                myConn.Open();
                myDataAdapter = new SqlDataAdapter(StrSql, myConn);
                myDataAdapter.Fill(myDataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return myDataSet;
        }
        public string ExecuteScalarSql(string StrSql)
        {
            object result = null;

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myCommand.CommandText = StrSql;
                myConn.Open();
                result = myCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            if (Convert.IsDBNull(result) || result == null)
            {
                result = "";
            }

            return result.ToString();
        }
        /// <summary>
        /// 执行单句Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public bool OperateDataACD(string StrSql)
        {
            bool bolResult = false;

            SqlConnection myConn = this.CreateDBConnection();
            SqlTransaction myTrans = null;
            SqlCommand myCommand = null;

            try
            {
                myConn.Open();

                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();

                myCommand.Transaction = myTrans;

                myCommand.CommandText = StrSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();

                bolResult = true;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }

                bolResult = false;

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return bolResult;
        }

        /// <summary>
        /// 同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="TranString"></param>
        /// <returns></returns>
        public bool ExecuteTran(ArrayList TranString)
        {
            if (TranString == null || TranString.Count == 0)
            {
                return false;
            }

            bool bolResult = false;
            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlTransaction myTrans = null;

            try
            {
                myConn.Open();
                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.Transaction = myTrans;

                foreach (string strSql in TranString)
                {
                    myCommand.CommandText = strSql;
                    myCommand.ExecuteNonQuery();
                }

                myTrans.Commit();
                bolResult = true;
            }
            catch (Exception ex)
            {
                bolResult = false;

                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return bolResult;
        }

        #endregion


        #region 基本数据库操作，以SqlItem对象的形式操作数据库。
        /// <summary>
        /// 使用SqlItem对象数组，同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="arySqlItem"></param>
        /// <returns></returns>
        public string ExecuteSqlItemArray(SqlItemArray arySqlItem)
        {
            List<SqlItem> list = arySqlItem.SqlItemList;

            if (list == null || list.Count == 0)
            {
                return "没有数据更改！";
            }

            string strResult = "";
            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlTransaction myTrans = null;

            try
            {
                myConn.Open();
                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.CommandTimeout = 180;
                myCommand.Transaction = myTrans;

                foreach (SqlItem item in list)
                {
                    if (this.CancelFlag == true)
                    {
                        throw new Exception("程序被强行取消！");
                    }

                    myCommand.CommandText = item.StrSql;
                    myCommand.Parameters.Clear();
                    if (item.StrSql == "p_session_parameters")
                    {
                        myCommand.CommandType = CommandType.StoredProcedure;
                        foreach (SqlParameters param in item.ParameterList)
                        {
                            SqlParameter SqlParameter = new SqlParameter(param.Name, param.Value);
                            SqlParameter.Direction = ParameterDirection.Input;
                            myCommand.Parameters.Add(SqlParameter);
                        }
                    }
                    else
                    {
                        myCommand.CommandType = CommandType.Text;
                        foreach (SqlParameters param in item.ParameterList)
                        {
                            if (param.Value.ToString() == "")
                            {
                                param.Value = DBNull.Value;
                            }
                            myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                        }
                    }
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                strResult = "OK";
            }
            catch (Exception ex)
            {
                strResult = "更新失败！";

                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return strResult;
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItem(SqlItem item)
        {
            DataSet ds = new DataSet();

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }

        /// <summary>
        /// 执行存储过程查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteStoreProc(SqlItem item)
        {
            DataSet ds = new DataSet();

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                myCommand.CommandTimeout = 180;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }

        /// <summary>
        /// 执行存储过程查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteStoreProc(SqlItem item,ref string pMessage)
        {
            DataSet ds = new DataSet();

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                myCommand.CommandTimeout = 180;
                foreach (SqlParameters param in item.ParameterList)
                {
                    SqlParameter _param = null;
                    if (param.Name == "@StrMessage")
                    {
                        _param = new SqlParameter(param.Name, SqlDbType.VarChar,100);
                        _param.Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        _param = new SqlParameter(param.Name, param.Value);
                        _param.Direction = ParameterDirection.Input;
                    }
                    myCommand.Parameters.Add(_param);
                }
                myAdapter.Fill(ds);
                pMessage = myCommand.Parameters["@StrMessage"].Value.ToString();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。connstr 数据库连接字符串
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItem(SqlItem item, string connStr)
        {
            DataSet ds = new DataSet();

            SqlConnection myConn = new SqlConnection(connStr);

            SqlCommand myCommand = null;
            SqlDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }


        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteScalarSqlItem(SqlItem item)
        {
            object result = null;

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();
                ;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            if (Convert.IsDBNull(result) || result == null)
            {
                result = "";
            }

            return result.ToString();
        }


        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteScalarSqlItem(SqlItem item, string connStr)
        {
            object result = null;

            SqlConnection myConn = new SqlConnection();
            myConn.ConnectionString = connStr;

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();
                ;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                return "";
            }
            finally
            {
                myConn.Close();
            }

            if (Convert.IsDBNull(result) || result == null)
            {
                result = "";
            }

            return result.ToString();
        }

        /// <summary>
        /// 执行SqlItem，并返回所影响行数。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlItem(SqlItem item)
        {
            int result = -1;

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// 执行写日志的存储过程，返回操作结果。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteLogProcedureSqlItem(SqlItem item)
        {
            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                //storedProcedure
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    SqlParameter oraParam = new SqlParameter(param.Name, param.Value);
                    switch (param.Name)
                    {
                        case "pSqlText":
                            oraParam.Direction = ParameterDirection.Input;
                            oraParam.Size = 50000;
                            break;
                        case "pSqlCode":
                            oraParam.Direction = ParameterDirection.Output;
                            oraParam.Size = 2000;
                            break;
                        case "pSqlErrm":
                            oraParam.Direction = ParameterDirection.Output;
                            oraParam.Size = 2000;
                            break;
                    }
                    myCommand.Parameters.Add(oraParam);
                }
                myConn.Open();
                myCommand.ExecuteNonQuery();

                return myCommand.Parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// 使用SqlItem对象数组，同时执行多个存储过程。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="arySqlItem"></param>
        /// <returns></returns>
        public string ExecuteProcedureSqlItemArray(SqlItemArray arySqlItem)
        {
            List<SqlItem> list = arySqlItem.SqlItemList;

            if (list == null || list.Count == 0)
            {
                return "没有数据更改！";
            }

            string strResult = "";
            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;
            SqlTransaction myTrans = null;

            try
            {
                myConn.Open();
                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.CommandTimeout = 180;
                myCommand.Transaction = myTrans;

                foreach (SqlItem item in list)
                {
                    if (this.CancelFlag == true)
                    {
                        throw new Exception("程序被强行取消！");
                    }
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = item.StrSql;
                    myCommand.Parameters.Clear();
                    foreach (SqlParameters param in item.ParameterList)
                    {
                        SqlParameter SqlParameter = new SqlParameter(param.Name, param.Value);
                        SqlParameter.Direction = ParameterDirection.Input;
                        myCommand.Parameters.Add(SqlParameter);
                    }
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                strResult = "OK";
            }
            catch (Exception ex)
            {
                strResult = "更新失败！";

                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return strResult;
        }
        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool ExecuteStoredProcedureSqlItem(SqlItem item)
        {
            bool result = false;

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myCommand.CommandTimeout = 180;
                //storedProcedure
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                if (item.StrSql == "p_exesql")
                {
                    foreach (SqlParameters param in item.ParameterList)
                    {
                        SqlParameter oraParam = new SqlParameter(param.Name, param.Value);
                        switch (param.Name)
                        {
                            case "pSqlText":
                                oraParam.Direction = ParameterDirection.Input;
                                break;
                            case "pSqlCode":
                                oraParam.Direction = ParameterDirection.Output;
                                break;
                            case "pSqlErrm":
                                oraParam.Direction = ParameterDirection.Output;
                                break;
                        }
                        myCommand.Parameters.Add(oraParam);
                    }
                }
                else
                {
                    foreach (SqlParameters param in item.ParameterList)
                    {
                        SqlParameter oraParam = new SqlParameter(param.Name, param.Value);
                        oraParam.Direction = ParameterDirection.Input;
                        myCommand.Parameters.Add(oraParam);
                    }
                }

                myConn.Open();
                myCommand.ExecuteNonQuery();

                result = true;
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool ExecuteStoredProcedureSqlItem(SqlItem item,ref string pMessage)
        {
            bool result = false;

            SqlConnection myConn = this.CreateDBConnection();

            SqlCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myCommand.CommandTimeout = 180;
                //storedProcedure
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    SqlParameter oraParam = null;
                    if (param.Name == "@StrMessage")
                    {
                        oraParam = new SqlParameter(param.Name, SqlDbType.VarChar, 100);
                        oraParam.Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        oraParam = new SqlParameter(param.Name, param.Value);
                        oraParam.Direction = ParameterDirection.Input;
                    }
                    myCommand.Parameters.Add(oraParam);
                }
                myConn.Open();
                myCommand.ExecuteNonQuery();
                pMessage = myCommand.Parameters["@StrMessage"].Value.ToString();
                result = true;
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }


        /// <summary>
        /// 执行存储过程，返回结果集。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteProcSqlItem(SqlItem item, ref int RowCount)
        {

            DataSet ds = new DataSet();
            SqlConnection myConn = this.CreateDBConnection();
            myConn.Open();
            SqlCommand myCommand = null;
            SqlDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    SqlParameter par = new SqlParameter(param.Name, param.Value);
                    if (param.Name == "@iRowCount")
                        par.Direction = ParameterDirection.Output;
                    else
                        par.Direction = ParameterDirection.Input;
                    myCommand.Parameters.Add(par);
                }
                myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                RowCount = Convert.ToInt32(myCommand.Parameters["@iRowCount"].Value);

            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }
        #endregion


        #region 以SqlItem对象的二进制流为参数进行数据库操作。
        /// <summary>
        /// 使用SqlItem对象数组，同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteSqlItemArrayBinary(byte[] item)
        {
            return ExecuteSqlItemArray(SqlBuilder.DeserializeSqlItemArray(item));
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItemBinary(byte[] item)
        {
            return ExecuteSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteProcSqlItem(byte[] item, ref int RowCount)
        {
            return ExecuteProcSqlItem(SqlBuilder.DeserializeSqlItem(item), ref RowCount);
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteStoreProc(byte[] item)
        {
            return ExecuteStoreProc(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteScalarSqlItemBinary(byte[] item)
        {
            return ExecuteScalarSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行SqlItem，并返回所影响行数。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlItemBinary(byte[] item)
        {
            return ExecuteNonQuerySqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool ExecuteStoredProcedureSqlItemBinary(byte[] item)
        {
            return ExecuteStoredProcedureSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteLogProcedureSqlItemBinary(byte[] item)
        {
            return ExecuteLogProcedureSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteProcedureSqlItemArrayBinary(byte[] item)
        {
            return ExecuteProcedureSqlItemArray(SqlBuilder.DeserializeSqlItemArray(item));
        }
        #endregion

        #region 批量插入及更新多条数据
        /// <summary>
        /// 批量插入及更新多条数据
        /// </summary>
        /// <param name="ds">批量插入、更新数据源</param>
        /// <param name="aryTableName">插入操作表名及排除列集合</param>
        /// <param name="updCol">更新操作表名及修改列集合</param>
        /// <returns></returns>
        public bool SqlBulkCopy(DataSet ds, Dictionary<string, string> aryTableName, SqlItem item,string dbName)
        {
            string[] connStrs = DBConnectionString.Split(new char[] { ';' });
            string _connStr = connStrs[0] + ";Initial Catalog=" + dbName + ";" + connStrs[2] + ";" + connStrs[3] + ";" + connStrs[4] + ";" + connStrs[5] + ";" + connStrs[6] + ";" + connStrs[7];
            SqlConnection myConn = new SqlConnection(_connStr);//this.CreateDBConnection();
            SqlTransaction trans = null;
            SqlCommand myCommand = null;
            try
            {
                myConn.Open();
                trans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.Transaction = trans;
                myCommand.CommandText = item.StrSql;
                myCommand.ExecuteNonQuery();
                //数据批量导入SqlBulkCopy
                SqlBulkCopy sqlbulk = new SqlBulkCopy(myConn, SqlBulkCopyOptions.Default, trans);
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (aryTableName.ContainsKey(ds.Tables[i].TableName))
                    {
                        if (string.IsNullOrEmpty(aryTableName[ds.Tables[i].TableName]))
                        {
                            //目标数据库表名
                            sqlbulk.BatchSize = 10000;
                            sqlbulk.BulkCopyTimeout = 60;
                            sqlbulk.ColumnMappings.Clear();
                            sqlbulk.DestinationTableName = ds.Tables[i].TableName;
                            //数据集字段索引与数据库字段索引映射
                            foreach (DataColumn col in ds.Tables[i].Columns)
                            {
                                sqlbulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            //导入
                            sqlbulk.WriteToServer(ds.Tables[i]);
                        }
                        else
                        {
                            sqlbulk.ColumnMappings.Clear();
                            sqlbulk.BatchSize = 10000;
                            sqlbulk.BulkCopyTimeout = 60;
                            List<string> lstExceptCols = new List<string>();
                            lstExceptCols.AddRange(aryTableName[ds.Tables[i].TableName].Split(','));
                            //目标数据库表名
                            sqlbulk.DestinationTableName = ds.Tables[i].TableName;
                            //数据集字段索引与数据库字段索引映射
                            foreach (DataColumn col in ds.Tables[i].Columns)
                            {
                                if (lstExceptCols.Contains(col.ColumnName)) continue;
                                sqlbulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            //导入
                            sqlbulk.WriteToServer(ds.Tables[i]);
                        }
                    }
                }
                trans.Commit();
                sqlbulk.Close();
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());
                return false;
            }
            finally
            {
                myConn.Close();
                ds.Dispose();
            }
        }

        public bool SqlBulkCopy(DataSet ds, Dictionary<string, string> aryTableName)
        {
            SqlConnection myConn = this.CreateDBConnection();
            SqlTransaction trans = null;
            try
            {

                //数据批量导入SqlBulkCopy
                myConn.Open();
                trans = myConn.BeginTransaction();
                //SqlBulkCopy sqlbulk = new SqlBulkCopy(DBConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                SqlBulkCopy sqlbulk = new SqlBulkCopy(myConn, SqlBulkCopyOptions.Default, trans);
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (aryTableName.ContainsKey(ds.Tables[i].TableName))
                    {
                        if (string.IsNullOrEmpty(aryTableName[ds.Tables[i].TableName]))
                        {
                            //目标数据库表名
                            sqlbulk.BatchSize = 10000;
                            sqlbulk.BulkCopyTimeout = 60;
                            sqlbulk.ColumnMappings.Clear();
                            sqlbulk.DestinationTableName = ds.Tables[i].TableName;
                            //数据集字段索引与数据库字段索引映射
                            foreach (DataColumn col in ds.Tables[i].Columns)
                            {
                                sqlbulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            //导入
                            sqlbulk.WriteToServer(ds.Tables[i]);
                        }
                        else
                        {
                            sqlbulk.ColumnMappings.Clear();
                            sqlbulk.BatchSize = 10000;
                            sqlbulk.BulkCopyTimeout = 60;
                            List<string> lstExceptCols = new List<string>();
                            lstExceptCols.AddRange(aryTableName[ds.Tables[i].TableName].Split(','));
                            //目标数据库表名
                            sqlbulk.DestinationTableName = ds.Tables[i].TableName;
                            //数据集字段索引与数据库字段索引映射
                            foreach (DataColumn col in ds.Tables[i].Columns)
                            {
                                if (lstExceptCols.Contains(col.ColumnName)) continue;
                                sqlbulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            //导入
                            sqlbulk.WriteToServer(ds.Tables[i]);
                        }
                    }

                }
                trans.Commit();
                sqlbulk.Close();
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());
                return false;
            }
            finally
            {
                myConn.Close();
                ds.Dispose();
            }
        }
        #endregion

        public int ExecuteNonQuerySqlItem(SqlItem item,string dbName)
        {
            int result = -1;
            string[] connStrs = DBConnectionString.Split(new char[] { ';' });
            string _connStr = connStrs[0] + ";Initial Catalog=" + dbName + ";" + connStrs[2] + ";" + connStrs[3] + ";" + connStrs[4] + ";" + connStrs[5] + ";" + connStrs[6] + ";" + connStrs[7];
            SqlConnection myConn = new SqlConnection(_connStr);
            SqlCommand myCommand = null;
            try
            {
                myCommand = myConn.CreateCommand();

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new SqlParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }
    }
}
